package com.best.oasis.settlement.util;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import com.best.oasis.settlement.common.enums.arAccount.ArAccountDetailType;
import com.best.oasis.settlement.common.enums.invoice.InvoiceRequestNoteStatus;
import com.best.oasis.settlement.common.enums.invoice.InvoiceRequestType;
import com.best.oasis.settlement.common.vo.customer.CustomerVO;
import com.best.oasis.settlement.common.vo.dealer.DealerVO;
import com.best.oasis.settlement.common.vo.invoice.InvoiceRequestNoteVO;
import com.best.oasis.settlement.common.vo.invoiceSend.InvoiceSendVO;


public class ARBalanceImport {
	
	public void importInvoice(Connection con, String fileName) throws Exception{
		FileInputStream inputStream = null;
		try {
			inputStream = new FileInputStream(fileName);
			POIFSFileSystem fs = new POIFSFileSystem(inputStream);
			HSSFWorkbook wb = new HSSFWorkbook(fs);
			HSSFSheet sheet = wb.getSheetAt(0);

			int index = 1;
			HSSFRow row = sheet.getRow(index);
			if(row==null){
				Logger.log("行" + index + "：数据为空。");
				return;
			}
			//验证通过的vo list
			List<InvoiceRequestNoteVO> invoices = new ArrayList<InvoiceRequestNoteVO>();
			while (row != null) {
				InvoiceRequestNoteVO invoice = createInvoice(con, row);
				if(invoice != null){
					invoices.add(invoice);
				}
				row = sheet.getRow(++index);
			}
			if(invoices.size() != (index - 1)){
				throw new Exception("数据验证出错，错误条数：" + (index - 1 - invoices.size()));
			}
			//发票
			StringBuffer sql = new StringBuffer("insert into st_invoicerequestnote(id,code,optlock,creator,invoicedate,invoiceno,invoiceTotalAmount,pendingChargeOfAmount")
			.append(",status,paycustomer_id,freight_sender_id,freight_reciever_id,requestType,createtime) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
			PreparedStatement i_ps = con.prepareStatement(sql.toString());
			//账户
			StringBuffer ar_sql = new StringBuffer("insert into st_araccountdetail(id, code, creator,optlock, amount, remainamount,effectivedate,invoiceno,type, customer_id,createtime) ")
			.append(" values(?,?,?,?,?,?,?,?,?,?,?)");
			PreparedStatement ar_ps = con.prepareStatement(ar_sql.toString());
			//发票寄送信息
			StringBuffer send_sql = new StringBuffer("insert into st_invoicesend(id, code, creator, createtime,optlock,expectpaydate,invoice_id) values (?,?,?,?,?,?,?)");
			PreparedStatement send_ps = con.prepareStatement(send_sql.toString());
			
			for(InvoiceRequestNoteVO invoice : invoices){
				//插入发票数据
				insertInvoice(i_ps, invoice);
				//插入寄送信息
				insertSendInfo(send_ps, invoice);
				//插入账户
				insertArAccount(ar_ps, invoice);
				//更新客户余额
				CustomerImport.updateCustomerBalance(con, invoice.getPayCustomer().getId(), invoice.getPendingChargeOfAmount());
			}
			
			
		} catch (Exception e) {
			throw e;
		} finally{
			try {
				inputStream.close();
			} catch (IOException e) {
			}
		}
	}
	//excel格式如下：
	//id	客户编码	客户名称	发货方	收货方	开票日期	发票号码	发票金额	待核销金额	发票备注	预计收款日期
	private InvoiceRequestNoteVO createInvoice(Connection con,HSSFRow row) throws Exception{
		InvoiceRequestNoteVO vo = new InvoiceRequestNoteVO();
		//id
		vo.setId(new Long(getCellText(row.getCell(0))));
		//发票申请编码
		vo.setCode(String.valueOf(vo.getId()));
		//发票编码
		vo.setCode(getCellText(row.getCell(0)));
		//客户名称
		String customerName = getCellText(row.getCell(2));
		CustomerVO customer = CustomerImport.getCustomter(con,customerName);
		if(customer == null){
			Logger.log("客户："+customerName+"，在系统中不存在！",row.getRowNum());
			return null;
		}
		vo.setPayCustomer(customer);
		//发货方
		String dealerName = getCellText(row.getCell(3));
		DealerVO dealer = CustomerImport.getDealer(con, dealerName, customer.getId());
		if(dealer == null){
			Logger.log("发货方："+dealerName+",在系统中不存在，或者不属于该客户！",row.getRowNum());
			return null;
		}
		vo.setFreightSender(dealer);
		//收货方
		String dealerName1 = getCellText(row.getCell(4));
		DealerVO dealer1 = CustomerImport.getDealer(con, dealerName, customer.getId());
		if(dealer1 == null){
			Logger.log("收货方："+dealerName1+",在系统中不存在，或者不属于该客户！！",row.getRowNum());
			return null;
		}
		vo.setFreightReciever(dealer1);
		//开票日期
		vo.setInvoiceDate(new SimpleDateFormat("yyyy-MM-dd").parse(getCellText(row.getCell(5))));
		//发票号码
		vo.setInvoiceNo(getCellText(row.getCell(6)));
		//发票金额
		vo.setInvoiceTotalAmount(new Double(getCellText(row.getCell(7))));
		//待核销金额
		vo.setPendingChargeOfAmount(new Double(getCellText(row.getCell(8))));
		//备注
		vo.setInvoiceRemark(getCellText(row.getCell(9)));
		
		//寄送信息，预计收款日期
		InvoiceSendVO sendInfo = new InvoiceSendVO();
		sendInfo.setId(vo.getId());
		sendInfo.setExpectPayDate(new SimpleDateFormat("yyyy-MM-dd").parse(getCellText(row.getCell(10))));
		vo.setSendInfo(sendInfo);
		return vo;
	}
	//插入发票数据
	private void insertInvoice(PreparedStatement ps, InvoiceRequestNoteVO vo) throws SQLException{
		ps.setLong(1,vo.getId());
		ps.setString(2,vo.getCode());
		ps.setInt(3,0);
		ps.setString(4,"admin");
		ps.setDate(5,new java.sql.Date(vo.getInvoiceDate().getTime()));
		ps.setString(6, vo.getInvoiceNo());
		ps.setDouble(7, vo.getInvoiceTotalAmount());
		ps.setDouble(8, vo.getPendingChargeOfAmount());
		//状态
		ps.setString(9, InvoiceRequestNoteStatus.INVOICED.toString());
		ps.setLong(10, vo.getPayCustomer().getId());
		ps.setLong(11, vo.getFreightSender().getId());
		ps.setLong(12, vo.getFreightReciever().getId());
		//申请类型
		ps.setString(13,InvoiceRequestType.NORMAL.toString());
		ps.setDate(14, new java.sql.Date(System.currentTimeMillis()));
		ps.executeUpdate();
	}
	//插入ARaccount
	private void insertArAccount(PreparedStatement ps, InvoiceRequestNoteVO vo) throws SQLException{

		ps.setLong(1,10000 + vo.getId());
		ps.setString(2, vo.getCode());
		ps.setString(3,"admin");
		ps.setInt(4,0);
		ps.setDouble(5, vo.getInvoiceTotalAmount());
		ps.setDouble(6, vo.getPendingChargeOfAmount());
		ps.setDate(7,new java.sql.Date(vo.getInvoiceDate().getTime()));
		ps.setString(8, vo.getInvoiceNo());
		ps.setString(9,ArAccountDetailType.INVOICE.toString());
		ps.setLong(10, vo.getPayCustomer().getId());
		ps.setDate(11, new java.sql.Date(System.currentTimeMillis()));
		ps.executeUpdate();
	}
	//插入发票寄送信息
	private void insertSendInfo(PreparedStatement ps, InvoiceRequestNoteVO vo) throws SQLException{
//		StringBuffer send_sql = new StringBuffer("insert into st_invoicesend(id, code, creator, cratetime,optlock,expectpaydate,invoice_id) values(?,?,?,?,?,?,?);");
		ps.setLong(1, vo.getSendInfo().getId());
		ps.setString(2, String.valueOf(vo.getSendInfo().getId()));
		ps.setString(3, "admin");
		ps.setDate(4, new java.sql.Date(System.currentTimeMillis()));
		ps.setInt(5, 0);
		ps.setDate(6, new java.sql.Date(vo.getSendInfo().getExpectPayDate().getTime()));
		ps.setLong(7, vo.getId());
		ps.executeUpdate();
	}
	
	
	/**
	 * 获取当前CELL内容
	 */
	private static String getCellText(HSSFCell cell) {
		if (cell == null)
			return "";
		int type = cell.getCellType();
		switch (type) {
		case HSSFCell.CELL_TYPE_BLANK:
			return "";
		case HSSFCell.CELL_TYPE_NUMERIC:
			double value = cell.getNumericCellValue();
			String tep = null;
			if (HSSFDateUtil.isCellDateFormatted(cell)) {    
		        Date date = HSSFDateUtil.getJavaDate(value);
		        tep = new SimpleDateFormat("yyyy-MM-dd").format(date);
		    }else{
				tep = Double.toString(value);
				if(tep.endsWith(".0")){
					tep = tep.substring(0, tep.length() - 2);
				}
		    }
			return tep; 
		case HSSFCell.CELL_TYPE_STRING:
			return cell.getRichStringCellValue().getString().trim();
		}
		return "";
	}
}
